﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="SessionLogService.svc.cs" company="Özgür Aytekin / Sandro Schnapp">
//   Özgür Aytekin / Sandro Schnapp
// </copyright>
// <summary>
//   Defines the SessionLogService type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using SqlSystemWatcher.Entities;

namespace SqlSystemWatcher.Service.Wcf
{
    /// <summary>
    ///     The session log service.
    /// </summary>
    public class SessionLogService : ISessionLogService
    {
        /// <summary>
        ///     The watcher config entry.
        /// </summary>
        private const string SqlWatcherConfig = "SqlSystemWatcher";

        /// <summary>
        ///     The get all session logs by wait time filter.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <param name="waitTimeFilter">
        ///     The wait time filter.
        /// </param>
        /// <returns>
        ///     The
        ///     <see>
        ///         <cref>List</cref>
        ///     </see>
        ///     .
        /// </returns>
        public List<SessionLogBaseEntity> GetAllSessionLogsByWaitTimeFilter(DateTime fromDate, DateTime toDate,
                                                                            int waitTimeFilter)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetAllSessionLogsByWaitTimeFilter]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));
                cmd.Parameters.Add(new SqlParameter("@WaitTimeFilter", waitTimeFilter));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<SessionLogBaseEntity>();

                while (reader.Read())
                {
                    int id = reader.GetInt32(reader.GetOrdinal("ID"));
                    string instanceName = reader.GetString(reader.GetOrdinal("InstanceName"));
                    string databaseName = reader.GetString(reader.GetOrdinal("DatabaseName"));
                    int waittime = reader.GetInt32(reader.GetOrdinal("WaitTime"));
                    DateTime createdDate = reader.GetDateTime(reader.GetOrdinal("LastModifiedDate"));

                    list.Add(
                        new SessionLogBaseEntity
                            {
                                HostName = instanceName,
                                ServerName = databaseName,
                                WaitTime = waittime,
                                CreatedDate = createdDate,
                                Id = id
                            });
                }

                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get all session logs by processor time filter.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <param name="waitTimeFilter">
        ///     The wait time filter.
        /// </param>
        /// <returns>
        ///     The
        ///     <see>
        ///         <cref>List</cref>
        ///     </see>
        ///     .
        /// </returns>
        public List<SessionLogBaseEntity> GetAllSessionLogsByCpuTimeFilter(DateTime fromDate, DateTime toDate,
                                                                           int waitTimeFilter)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetAllSessionLogsByCpuTimeFilter]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));
                cmd.Parameters.Add(new SqlParameter("@CpuTimeFilter", waitTimeFilter));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<SessionLogBaseEntity>();

                while (reader.Read())
                {
                    int id = reader.GetInt32(reader.GetOrdinal("ID"));
                    string instanceName = reader.GetString(reader.GetOrdinal("InstanceName"));
                    string databaseName = reader.GetString(reader.GetOrdinal("DatabaseName"));
                    int cputime = reader.GetInt32(reader.GetOrdinal("CpuTime"));
                    DateTime createdDate = reader.GetDateTime(reader.GetOrdinal("LastModifiedDate"));

                    list.Add(new SessionLogBaseEntity
                        {
                            HostName = instanceName,
                            ServerName = databaseName,
                            CpuTime = cputime,
                            CreatedDate = createdDate,
                            Id = id
                        });
                }

                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get all session logs by reads filter.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <param name="readsFilter">
        ///     The reads filter.
        /// </param>
        /// <returns>
        ///     The
        ///     <see>
        ///         <cref>List</cref>
        ///     </see>
        ///     .
        /// </returns>
        public List<SessionLogBaseEntity> GetAllSessionLogsByReadsFilter(DateTime fromDate, DateTime toDate,
                                                                         int readsFilter)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetAllSessionLogsByReadsFilter]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));
                cmd.Parameters.Add(new SqlParameter("@ReadsFilter", readsFilter));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<SessionLogBaseEntity>();

                while (reader.Read())
                {
                    int id = reader.GetInt32(reader.GetOrdinal("ID"));
                    string instanceName = reader.GetString(reader.GetOrdinal("InstanceName"));
                    string databaseName = reader.GetString(reader.GetOrdinal("DatabaseName"));
                    int reads = reader.GetInt32(reader.GetOrdinal("Reads"));
                    int writes = reader.GetInt32(reader.GetOrdinal("Writes"));
                    DateTime createdDate = reader.GetDateTime(reader.GetOrdinal("LastModifiedDate"));

                    list.Add(
                        new SessionLogBaseEntity
                            {
                                HostName = instanceName,
                                ServerName = databaseName,
                                Reads = reads,
                                Writes = writes,
                                CreatedDate = createdDate,
                                Id = id
                            });
                }

                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get top wait time count.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     Wait Time on Top
        /// </returns>
        public int? GetTopWaitTimeCount(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetTopWaitCount]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                object waitTime = cmd.ExecuteScalar();

                return Convert.ToInt32(waitTime);
            }
        }

        /// <summary>
        ///     The get count of all servers over maximum processor time.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <param name="limit">
        ///     The limit.
        /// </param>
        /// <param name="type">
        ///     The type.
        /// </param>
        /// <returns>
        ///     The <see cref="int" />.
        /// </returns>
        public int GetCountOfAllServersOverMaximumByType(DateTime fromDate, DateTime toDate, int limit, string type)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetCountOfAllServersOverMaximumByType]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));
                cmd.Parameters.Add(new SqlParameter("@Limit", limit));
                cmd.Parameters.Add(new SqlParameter("@Type", type));

                object count = cmd.ExecuteScalar();

                return Convert.ToInt32(count);
            }
        }

        /// <summary>
        ///     The get maximum processor time with instance name.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     The
        ///     <see>
        ///         <cref>List</cref>
        ///     </see>
        ///     .
        /// </returns>
        public List<string> GetMaximumCpuTimeWithInstanceName(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetMaximumCpuTimeWithInstanceName]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<string>();

                while (reader.Read())
                {
                    string instanceName = reader.GetString(reader.GetOrdinal("InstanceName"));
                    string databaseName = reader.GetString(reader.GetOrdinal("DatabaseName"));
                    string cpuTime = reader.GetInt32(reader.GetOrdinal("CpuTime")).ToString();

                    list.Add(instanceName);
                    list.Add(cpuTime);
                }
                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get maximum reads with instance name.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     The <see cref="List" />.
        /// </returns>
        public List<string> GetMaximumReadsWithInstanceName(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetMaximumReadsWithInstanceName]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<string>();

                while (reader.Read())
                {
                    string instanceName = reader.GetString(reader.GetOrdinal("InstanceName"));
                    string reads = reader.GetInt32(reader.GetOrdinal("Reads")).ToString();

                    list.Add(instanceName);
                    list.Add(reads);
                }
                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get maximum writes with instance name.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     The <see cref="List" />.
        /// </returns>
        public List<string> GetMaximumWritesWithInstanceName(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetMaximumWritesWithInstanceName]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<string>();

                while (reader.Read())
                {
                    string instanceName = reader.GetString(reader.GetOrdinal("InstanceName"));
                    string writes = reader.GetInt32(reader.GetOrdinal("Writes")).ToString();

                    list.Add(instanceName);
                    list.Add(writes);
                }
                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get maximum wait time with instance name.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     The <see cref="List" />.
        /// </returns>
        public List<string> GetMaximumWaitTimeWithInstanceName(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetMaximumWaitTimeWithInstanceName]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<string>();

                while (reader.Read())
                {
                    string instanceName = reader.GetString(reader.GetOrdinal("InstanceName"));
                    string waitTime = reader.GetInt32(reader.GetOrdinal("WaitTime")).ToString();

                    list.Add(instanceName);
                    list.Add(waitTime);
                }
                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get count of all servers.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     The <see cref="int" />.
        /// </returns>
        public int GetCountOfAllServers(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetCountOfAllServers]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                object count = cmd.ExecuteScalar();

                return Convert.ToInt32(count);
            }
        }

        /// <summary>
        ///     The get top wait time server and time.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     The
        ///     <see>
        ///         <cref>List</cref>
        ///     </see>
        ///     .
        /// </returns>
        public List<string> GetTopWaitTimeServerAndTime(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetTopWaitTimeServerAndTime]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<string>();

                while (reader.Read())
                {
                    string instanceName = reader.GetString(reader.GetOrdinal("InstanceName"));
                    string databaseName = reader.GetString(reader.GetOrdinal("DatabaseName"));
                    string waittime = reader.GetInt32(reader.GetOrdinal("WaitTime")).ToString();

                    list.Add(instanceName);
                    list.Add(waittime);
                }
                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get servers over the timeout limit count.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <param name="limit">
        ///     The limit.
        /// </param>
        /// <returns>
        ///     The <see cref="int" />.
        /// </returns>
        public int GetServersOverTheTimeoutLimitCount(DateTime fromDate, DateTime toDate, int limit)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetServersOverTheTimeoutLimitCount]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));
                cmd.Parameters.Add(new SqlParameter("@Limit", limit));

                object count = cmd.ExecuteScalar();

                return Convert.ToInt32(count);
            }
        }

        /// <summary>
        ///     The get session log average and max read time.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     The <see cref="List" />.
        /// </returns>
        public List<int> GetSessionLogAverageAndMaxReadTime(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetSessionLogAverageAndMaxReadTime]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<int>();

                while (reader.Read())
                {
                    int maxWaitTime = reader.GetInt32(reader.GetOrdinal("MaxReadTime"));
                    int averageWaitTime = reader.GetInt32(reader.GetOrdinal("AverageReadTime"));

                    list.Add(maxWaitTime);
                    list.Add(averageWaitTime);
                }
                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get session log average and max write time.
        /// </summary>
        /// <param name="fromDate">
        ///     The from date.
        /// </param>
        /// <param name="toDate">
        ///     The to date.
        /// </param>
        /// <returns>
        ///     The <see cref="List" />.
        /// </returns>
        public List<int> GetSessionLogAverageAndMaxWriteTime(DateTime fromDate, DateTime toDate)
        {
            using (SqlConnection con = GetNewSqlConnection())
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "[svc].[GetSessionLogAverageAndMaxWriteTime]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Fromdate", fromDate));
                cmd.Parameters.Add(new SqlParameter("@ToDate", toDate));

                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                var list = new List<int>();

                while (reader.Read())
                {
                    int maxWaitTime = reader.GetInt32(reader.GetOrdinal("MaxWriteTime"));
                    int averageWaitTime = reader.GetInt32(reader.GetOrdinal("AverageWriteTime"));

                    list.Add(maxWaitTime);
                    list.Add(averageWaitTime);
                }
                reader.Close();

                return list;
            }
        }

        /// <summary>
        ///     The get new connection to database.
        /// </summary>
        /// <returns>
        ///     The <see cref="SqlConnection" />.
        /// </returns>
        private SqlConnection GetNewSqlConnection()
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings[SqlWatcherConfig].ConnectionString);
        }
    }
}